package util;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONException;
import com.alibaba.fastjson.JSONObject;
import lombok.extern.slf4j.Slf4j;

import java.sql.*;

/**
 * 提供JDBC连接对象和释放资源
 */
@Slf4j
public final class JdbcHelper {
    private static String url = "jdbc:mysql://localhost:3306/bysj" +
            "?useUnicode=true&characterEncoding=utf8" + //疑问后面指定代码为UTF-8
            "&serverTimezone=Asia/Shanghai";//服务器时区为上海
    private static String user = "root";
    private static String password = "240828chen";
    //jdbc:mysql://localhost:3306/bysj?useUnicode=true&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC

    // 不允许创建本类对象
    private JdbcHelper() {
    }

    //注册驱动
    static {
        try {
            //Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            //注册驱动程序
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            System.out.println("未找到驱动程序类");
        }
    }

    /**
     * @return 连接对象
     * @throws SQLException
     */
    public static Connection getConn() throws SQLException {
        return DriverManager.getConnection(url, user, password);
    }

    //关闭、释放资源
    public static void close(ResultSet rs, Statement stmt, Connection conn) {
        try {
            if (rs != null) {
                rs.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    if (conn != null) {
                        conn.close();
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    //关闭、释放资源
    public static void close(ResultSet rs, Statement stmt) {
        JdbcHelper.close(rs, stmt, null);
    }

    //关闭、释放资源
    public static void close(Statement stmt, Connection conn) {
        JdbcHelper.close(null, stmt, conn);
    }

    //关闭、释放资源
    public static void close(Connection conn) {
        JdbcHelper.close(null, null, conn);
    }

    //关闭、释放资源
    public static void close(Statement stmt) {
        JdbcHelper.close(null, stmt, null);
    }

    public static String resultSetToJson(ResultSet rs) throws SQLException, JSONException {
        // json数组
        JSONArray array = new JSONArray();
        // 获取列数
        ResultSetMetaData metaData = rs.getMetaData();
        int columnCount = metaData.getColumnCount();
        // 遍历ResultSet中的每条数据
        while (rs.next()) {
            JSONObject jsonObj = new JSONObject();
            // 遍历每一列
            for (int i = 1; i <= columnCount; i++) {
                String columnName = metaData.getColumnLabel(i);
                String value = rs.getString(columnName);
                jsonObj.put(columnName, value);
            }
            array.add(jsonObj);
        }
        return array.toString();
    }
}
